package persistence.article.articleDAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;

import dbExceptions.DataBaseErrorException;
import persistence.DAO.DAOHelper;
import persistence.combo.comboDAO.ComboDAO;
import article.exceptions.BadArticleException;
import article.exceptions.BadCategoryException;
import article.exceptions.MissingArticleException;
import article.exceptions.MissingCategoryException;
import article.exceptions.NotHereException;
import article.exceptions.badClassException;
import business.article.entities.Article;
import business.article.entities.Category;
import business.article.entities.Ingredient;

public class ArticleDAO {

	/**
	 * Singleton implementation begin
	 */
	
	private static ArticleDAO instance = null;

	private ArticleDAO() {
	}

	public static ArticleDAO getInstance() {
		if (instance == null)
			instance = new ArticleDAO();
		return instance;
	}

	/**
	 * Singleton implementation end
	 * 
	 * @throws badClassException
	 */
/**
 * @throws MissingCategoryException 
 * @throws DataBaseErrorException 
 * @throws BadCategoryException 
 * @throws MissingArticleException 
 * ***********************************************/
	public void addArticle(Article artNew) throws MissingCategoryException, DataBaseErrorException, BadCategoryException, MissingArticleException {
		if (artNew==null)
			throw new MissingArticleException("MissingArticleException");
		if (artNew.getCat()==null) throw new MissingCategoryException("MissingCategoryException");
		if (!categoryExists(artNew.getCat())){ 
			throw new BadCategoryException();
		}
		Statement st1 = null;
		Statement st2 = null;
		Statement st3 = null;
		Statement st4 = null;
		Connection con=null;

		try {
			con=DAOHelper.getInstance().getConnection();
			Integer[] id;
			id = getClassID(artNew.getCat().getPrimaryKey());
		
		Integer articulo_id = getNewID(id);
		st1=con.createStatement();
		st1.execute("INSERT INTO Article (name,price,id_category,primary_key,description) VALUES('"
				+ artNew.getName()
				+ "',"
				+ String.valueOf(artNew.getPrice())
				+ ","
				+ String.valueOf(id[1])
				+ ","
				+ String.valueOf(articulo_id)
				+ ",'"
				+ artNew.getDesc() + "')");
		st1.close();
		st2=con.createStatement();
		ResultSet s = st2.executeQuery("SELECT inner_key FROM Article WHERE primary_key="+ String.valueOf(articulo_id));
		s.next();
		int inner_id_articulo = s.getInt(1);
		st2.close();
		
		st3=con.createStatement();
		st3.execute("INSERT INTO Atomizable_unit (inner_key) VALUES("+inner_id_articulo+")");
		st3.close();
		if (artNew.getIngr()==null)return;
		for (Ingredient i : artNew.getIngr()) {
		st4=con.createStatement();
			st4.execute(
					"INSERT INTO Article_Ingredient " +
					"(id_atomizable_unit,id_ingredient) VALUES("
					+ String.valueOf(inner_id_articulo)
					+ ","
					+ String.valueOf(i.getId()) + ")");
		}
		st4.close();
		
		} catch (badClassException e) {
			
			e.printStackTrace();
		}catch (SQLException e) {
			
			e.printStackTrace();
		} finally{
			if(con!=null){try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}}
		}
	
	}

	private Integer[] getClassID(int classId) throws badClassException {
		Statement st=null; 
		Connection con=null;
		try {
			con =DAOHelper.getInstance().getConnection();
			st=con.createStatement();
			ResultSet r = st.executeQuery("SELECT primary_key,inner_key FROM Category WHERE primary_key="
							+ String.valueOf(classId));
			if (!r.next())
				throw new badClassException();
			Integer[] inte = new Integer[2];
			inte[0] = r.getInt(1);
			inte[1] = r.getInt(2);
			st.close();
			return inte;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			 if (con != null) { try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} }
		}
		return null;
	}

	private Integer getNewID(Integer[] classIDs) {
		Statement st=null;
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			st=con.createStatement();
			ResultSet r = st.executeQuery("SELECT primary_key FROM Article WHERE id_category="
							+ String.valueOf(classIDs[1])+ " ORDER BY inner_key ASC");

			ArrayList<Integer> articulos = new ArrayList<Integer>();
			while (r.next()) {
				articulos.add(r.getInt(1));
			}
			st.close();
			
			
			if (articulos.size()==0)
				return classIDs[0] * 1000;
			Collections.sort(articulos);
			
		
			
			int i = 0;
			for (Integer j : articulos) {
				
				if (i + 1000 * classIDs[0] != j)
					break;
				i++;
			}
			return i + 1000 * classIDs[0];

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			 if (con != null) { try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} }
		}
		return null;
	}
 
	private boolean categoryExists(Category c) throws DataBaseErrorException{
		Statement st=null;
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			st=con.createStatement();
			ResultSet r=st.executeQuery("SELECT COUNT(primary_key) FROM Category WHERE primary_key="+String.valueOf(c.getPrimaryKey()));
			r.next();
			int a=r.getInt(1);
			if (a==0) return false;
			if (a==1) return true;
			if (a>1) throw new DataBaseErrorException();  
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			 if (con != null) { try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} }
		}
		return false;
		
		
	}
/**
 * @throws MissingCategoryException 
 * @throws BadCategoryException 
 * @throws DataBaseErrorException 
 * @throws MissingArticleException 
 * @throws BadArticleException 
 * ***********************************************/	
	public void updateArticle(Article artUpt) throws NotHereException, MissingCategoryException, DataBaseErrorException, BadCategoryException, MissingArticleException, BadArticleException {
		
		if (artUpt==null)
			throw new MissingArticleException("MissingArticleException");
		if (artUpt.getCat()==null) throw new MissingCategoryException("MissingCategoryException");
		if (!categoryExists(artUpt.getCat())){
			throw new BadCategoryException();
		}
		if(!articleExists(artUpt)){
			throw new BadArticleException();
		}
		
		String sql = "UPDATE Article SET ";
		sql = sql + "name='" + artUpt.getName() + "'";
		if (artUpt.getDesc() == null) {
			sql = sql + ", description=" + "null";
		} else {
			sql = sql + ", description=" + "'" + artUpt.getDesc() + "'";
		}
		sql = sql + ", price=" + String.valueOf(artUpt.getPrice());
		
		sql = sql + ", id_category="
				+ String.valueOf(getCat_id(artUpt.getCat().getPrimaryKey()));
		sql = sql + " WHERE primary_key="
				+ String.valueOf(artUpt.getPrimaryKey());
		
		Statement st1=null;
		Statement st2=null;
		Statement st3=null;
		Statement st4=null;
		ResultSet s;
		Connection con=null;
		
		try {
			con=DAOHelper.getInstance().getConnection();
			st1=con.createStatement();
			st1.executeUpdate(sql);
			st1.close();
			
			st2=con.createStatement();
			s = st2.executeQuery("SELECT inner_key FROM Article WHERE primary_key="
							+ String.valueOf(artUpt.getPrimaryKey()));
			s.next();
			int inner_id_articulo = s.getInt(1);
			st2.close();

			st3=con.createStatement();
			st3.execute("DELETE FROM Article_Ingredient WHERE id_atomizable_unit="
							+ String.valueOf(inner_id_articulo));

			for (Ingredient i : artUpt.getIngr()) {
			st4=con.createStatement();
			st4.execute("INSERT INTO Article_Ingredient (id_atomizable_unit,id_ingredient) VALUES("
									+ String.valueOf(inner_id_articulo)
									+ ","
									+ String.valueOf(i.getId()) + ")");

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			 if (con != null) { try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} }
			
		}

	}

	@SuppressWarnings("null")
	private int getCat_id(int i) {
		Connection con=null;
		try {
			Statement st1=null;
			st1=con.createStatement();
			ResultSet s = st1.executeQuery("SELECT inner_key FROM Category WHERE Category.primary_key="
							+ String.valueOf(i) );
			s.next();
			int id = s.getInt(1);
			return id;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}finally{
			 if (con != null) { try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} }
		}

	}

	@SuppressWarnings("null")
	private boolean articleExists(Article c) throws DataBaseErrorException{
		Connection con=null;
		Statement st1=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			st1=con.createStatement();
			ResultSet r=st1.executeQuery("SELECT COUNT(primary_key) FROM Article WHERE primary_key="+String.valueOf(c.getPrimaryKey()));
			r.next();
			int a=r.getInt(1);
			if (a==0) return false;
			if (a==1) return true;
			if (a>1) throw new DataBaseErrorException();  
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			 if (con != null) { try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} }
		}
		return false;
		
		
	}

/**
 * @throws MissingArticleException 
 * @throws BadArticleException 
 * @throws DataBaseErrorException 
 * ***********************************************/
	public void deleteArticle(Article artDel) throws MissingArticleException, BadArticleException, DataBaseErrorException {
		if (artDel==null)
			throw new MissingArticleException("MissingArticleException");
		if(!articleExists(artDel)){
			throw new BadArticleException();
		}
		String sql = "UPDATE Article SET ";
		sql = sql + " active=0";
		sql = sql + ", primary_key=-1";
		sql = sql + ", id_category=-1";
		sql = sql + " WHERE primary_key="
				+ String.valueOf(artDel.getPrimaryKey());
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement st=null;
			st=con.createStatement();
			st.executeUpdate(sql);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			 if (con != null) { try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} }
		}
	}

/*************************************************/
	public Article[] getArticles() {
		Connection con=null;
		
		  try {
			  con=DAOHelper.getInstance().getConnection();
			  Statement st1=con.createStatement();
			  ResultSet r=st1.executeQuery(
		  "SELECT name,description,primary_key,price FROM Article WHERE active=1 AND id_category>0");
		  ArrayList<Article> articulos=new ArrayList<Article>();
		  
		  while(r.next()){articulos.add(new Article(r.getString(1),
		  r.getString(2), r.getInt(3), r.getDouble(4),
		  getIngredients(r.getInt(3)),
		  getCategory(r.getInt(3)))); }
		  
		  Article[] ingr=new Article[articulos.size()];
		  articulos.toArray(ingr); return ingr; } 
		  catch (SQLException e) { e.printStackTrace();
		  }finally{
			  if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		  }
		  
		  return null;
		 
	}

	private Ingredient[] getIngredients(int articleID) {
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement stat = con.createStatement();
			ResultSet rs = stat
					.executeQuery("SELECT Ingredient.name,Ingredient.in_stock,Ingredient.inner_key FROM Ingredient " +
							"JOIN Article_Ingredient ON Article_Ingredient.id_ingredient=Ingredient.inner_key " +
							"JOIN Atomizable_unit ON Atomizable_unit.inner_key=Article_Ingredient.id_atomizable_unit " +
							"JOIN Article ON Article.inner_key=Atomizable_unit.inner_key" +
							" WHERE Article.primary_key="
							+ String.valueOf(articleID));
			ArrayList<Ingredient> ingredientes = new ArrayList<Ingredient>();
			while (rs.next()) {
				ingredientes.add(new Ingredient(rs.getString(1), rs.getBoolean(2), rs.getInt(3)));
			}
			stat.close();
			Ingredient[] ingr = new Ingredient[ingredientes.size()];
			ingredientes.toArray(ingr);
			return ingr;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
		}

		return null;
	}

	private Category getCategory(int articleID) {
		Connection con=null;
		try {
			con=DAOHelper.getInstance().getConnection();
			Statement stat = con.createStatement();
			ResultSet rs = stat
					.executeQuery("SELECT Category.name,Category.primary_key FROM Category JOIN Article ON Article.id_category=Category.inner_key WHERE Article.primary_key="
							+ String.valueOf(articleID));
			rs.next();
			Category c = new Category(rs.getString(1), rs.getInt(2));
			stat.close();
			return c;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
		}
		return null;

	}
	
	public ArrayList<Article> getAllArticles(){
		 ArrayList<Article> a=new  ArrayList<Article>();
		 Collections.addAll(a, ArticleDAO.getInstance().getArticles());
		 Collections.addAll(a, ComboDAO.getInstance().getCombos());
	return a;
	}


	

}
